USE MASTER 
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'DHAA')
BEGIN
PRINT N'Đã tồn tại DB 		[DHAA]';
DROP DATABASE DHAA;
PRINT N'Xoá thành công DB 	[DHAA]';
END
GO

BEGIN
PRINT N'Đang khởi tạo DB 	[DHAA]';
CREATE DATABASE DHAA
END
GO

USE DHAA
GO
PRINT N'*********		Tạo bảng		*********';
BEGIN
CREATE TABLE [dbo].[Admins] (
[AdminID] int PRIMARY KEY NOT NULL IDENTITY(1,1) ,
[UserName] varchar(50) NOT NULL ,
[PassWord] varchar(32) NOT NULL ,
[AdminName] nvarchar(100) NULL DEFAULT ('Administrator') 
)

PRINT N'Tạo bảng 	[Admins]... 	Thành công';
END
GO

BEGIN

CREATE TABLE [dbo].[Categories] (
[CategoryID] int PRIMARY KEY NOT NULL IDENTITY(1,1) ,
[CategoryName] nvarchar(250) NOT NULL ,
[ParentID] int NOT NULL DEFAULT ((0)) ,
[Image] varchar(150) NULL ,
[Description] nvarchar(MAX) NULL 
)
PRINT N'Tạo bảng 	[Categories]...	Thành công';
END
GO
BEGIN
CREATE TABLE [dbo].[Customers] (
[CustomerID] int PRIMARY KEY NOT NULL IDENTITY(1,1),
[UserName] varchar(50) NOT NULL ,
[Password] varchar(32) NOT NULL ,
[CustomerName] nvarchar(100) NOT NULL 
)
PRINT N'Tạo bảng 	[Customers]... 	Thành công';
END
GO

BEGIN
CREATE TABLE [dbo].[FeedBack] (
[FeedID] int PRIMARY KEY NOT NULL IDENTITY(1,1) ,
[FeedContent] nvarchar(250) NOT NULL ,
[CustomerID] int NOT NULL ,
[ProductID] int NOT NULL ,
[FeedDate] datetime NOT NULL DEFAULT '' 
)
PRINT N'Tạo bảng 	[FeedBack]...	Thành công!';
END
GO



BEGIN
CREATE TABLE [dbo].[Products] (
[ProductID] int PRIMARY KEY NOT NULL IDENTITY(1,1) ,
[CategoryID] int NOT NULL ,
[ProductName] nvarchar(250) NOT NULL ,
[ProductImage] varchar(100) NOT NULL ,
[Price] float(53) NOT NULL ,
[Description] nvarchar(MAX) NULL ,
[FeedBackID] int NULL 
)
PRINT N'Tạo bảng 	[Products]...	Thành công!';
END

GO

PRINT N'*********	Khoá phụ		*********';
GO
BEGIN
ALTER TABLE [dbo].[FeedBack] ADD FOREIGN KEY ([CustomerID]) REFERENCES [dbo].[Customers] ([CustomerID]) ON DELETE NO ACTION ON UPDATE NO ACTION
PRINT N'Khoá 	[FeedBack].[CustomerID] 	-> 	[Customers].[CustomerID] 	được thêm thành công';
END
GO
BEGIN
ALTER TABLE [dbo].[FeedBack] ADD FOREIGN KEY ([ProductID]) REFERENCES [dbo].[Products] ([ProductID]) ON DELETE NO ACTION ON UPDATE NO ACTION
PRINT N'Khoá 	[FeedBack].[ProductID] 		-> 	[Products].[ProductID] 		được thêm thành công';
END
GO
BEGIN
ALTER TABLE [dbo].[Products] ADD FOREIGN KEY ([CategoryID]) REFERENCES [dbo].[Categories] ([CategoryID]) ON DELETE NO ACTION ON UPDATE NO ACTION
PRINT N'Khoá 	[Products].[CategoryID] 	-> 	[Categories].[CategoryID] 	được thêm thành công';
END
GO


PRINT N'*********	 PROCEDURE 		*********';
GO

CREATE PROCEDURE [dbo].[AdminDetailsByID]
(
	@AdminID int
)
AS
	SET NOCOUNT ON;
SELECT        AdminID, UserName, PassWord, AdminName
FROM            Admins
WHERE        (AdminID = @AdminID)
GO
PRINT N'Proc [AdminDetailsByID] 		đã được tạo.';
GO

CREATE PROCEDURE [dbo].[AdminLogin]
(
	@Usernama varchar(50),
	@Password varchar(32)
)
AS
	SET NOCOUNT ON;
SELECT        AdminID, UserName, AdminName, PassWord
FROM            Admins
WHERE        (UserName = @Usernama) AND (PassWord = @Password)

GO
PRINT N'Proc [AdminLogin] 				đã được tạo.';  
GO

CREATE PROCEDURE [dbo].[DeleteCustomerByID]
(
	@CustomerID int
)
AS
	SET NOCOUNT OFF;
DELETE FROM [dbo].[Customers] WHERE (([CustomerID] = @CustomerID))

GO
PRINT N'Proc [DeleteCustomerByID] 		đã được tạo.';
GO

CREATE PROCEDURE [dbo].[GetCategoryByParentID]
(
	@ParentID int
)
AS
SELECT * FROM Categories WHERE ParentID = @ParentID

GO

PRINT N'Proc [GetCategoryByParentID] 	đã được tạo.';
GO


CREATE PROCEDURE [dbo].[InsertAdmin]
(
	@UserName varchar(50),
	@PassWord varchar(32),
	@AdminName nvarchar(100)
)
AS
	SET NOCOUNT OFF;
INSERT INTO [dbo].[Admins] ([UserName], [PassWord], [AdminName]) VALUES (@UserName, @PassWord, @AdminName)
GO

PRINT N'Proc [InsertAdmin] 				đã được tạo.';
GO


CREATE PROCEDURE [dbo].[InsertCustomer]
(
	@UserName varchar(50),
	@Password varchar(32),
	@CustomerName nvarchar(100)
)
AS
	SET NOCOUNT OFF;
INSERT INTO Customers
                         (UserName, Password, CustomerName)
VALUES        (@UserName,@Password,@CustomerName)
GO

PRINT N'Proc [InsertCustomer] 			đã được tạo.';
GO


CREATE PROCEDURE [dbo].[SelectCategoryByID]
(
	@CategoryID int
)
AS
	SET NOCOUNT ON;
SELECT        CategoryID, CategoryName, ParentID, Image, Description
FROM            Categories
WHERE        (CategoryID = @CategoryID)
GO
PRINT N'Proc [SelectCategoryByID] 		đã được tạo.';
GO


CREATE PROCEDURE [dbo].[SelectCategoryByParentID]
(
	@ParentID int
)
AS
	SET NOCOUNT ON;
SELECT        CategoryID, CategoryName, ParentID, Image, Description
FROM            Categories
WHERE        (ParentID = @ParentID)
GO
PRINT N'Proc [SelectCategoryByParentID]	đã được tạo.';
GO


CREATE PROCEDURE [dbo].[SelectCustomerByID]
(
	@CustomerID int
)
AS
	SET NOCOUNT ON;
SELECT CustomerID, UserName, Password, CustomerName FROM dbo.Customers WHERE (CustomerID = @CustomerID)
GO
PRINT N'Proc [SelectCustomerByID] 		đã được tạo.';
GO



CREATE PROCEDURE [dbo].[UpdateAdminInfo]
(
	@UserName varchar(50),
	@PassWord varchar(32),
	@AdminName nvarchar(100),
	@AdminID int
)
AS
	SET NOCOUNT OFF;
UPDATE       Admins
SET                UserName = @UserName, PassWord = @PassWord, AdminName = @AdminName
WHERE        (AdminID = @AdminID)
GO

PRINT N'Proc [UpdateAdminInfo] 			đã được tạo.';
GO


CREATE PROCEDURE [dbo].[UpdateCustomerByID]
(
	@UserName varchar(50),
	@Password varchar(32),
	@CustomerName nvarchar(100),
	@CustomerID int
)
AS
	SET NOCOUNT OFF;
UPDATE [dbo].[Customers] SET [UserName] = @UserName, [Password] = @Password, [CustomerName] = @CustomerName WHERE (([CustomerID] = @CustomerID))
GO
PRINT N'Proc [UpdateCustomerByID] 		đã được tạo.';
GO



PRINT N'*********	Chèn bản ghi dữ liệu	*********';
-- ----------------------------
-- INSERT --
-- ----------------------------
INSERT INTO [dbo].[Admins] VALUES (N'nhahv', N'202cb962ac5975b964b7152d234b70', N'Nhã HV');
GO
INSERT INTO [dbo].[Admins] VALUES (N'admin', N'202cb962ac5975b964b7152d234b70', N'Administrator');
GO
GO

-- ----------------------------
-- Records of Categories
-- ----------------------------
INSERT INTO [dbo].[Categories] VALUES ('Máy tính', N'0', N'123', N'Máy tính');
GO
INSERT INTO [dbo].[Categories] VALUES ('Điện thoại', N'0', N'123', N'Điện Thoại');
GO
INSERT INTO [dbo].[Categories] VALUES ('Destop', N'1', N'123', N'Destop');
GO
INSERT INTO [dbo].[Categories] VALUES ('Tablet', N'1', N'123', N'Tablet PC');
GO
INSERT INTO [dbo].[Categories] VALUES ('HTC', N'2', N'123', N'HTC');
GO
INSERT INTO [dbo].[Categories] VALUES (N'IPhone', N'2', N'123', N'Iphone');
GO
INSERT INTO [dbo].[Categories] VALUES (N'ATT', N'2', N'123', N'AT&T');
GO
INSERT INTO [dbo].[Categories] VALUES (N'ASUS', N'5', N'123', N'sss');
GO
INSERT INTO [dbo].[Categories] VALUES (N'ACER', N'5', N'2123', N'SSDF');
GO
INSERT INTO [dbo].[Categories] VALUES (N'APPLE', N'5', N'4324', N'145GHH');
GO
INSERT INTO [dbo].[Categories] VALUES (N'GAMMING PC', N'17', N'ASUS', N'KHông có');
GO
INSERT INTO [dbo].[Categories] VALUES (N'ENTERTAIMENT PC', N'17', N'ENTER', N'Vãi hòm');
GO
INSERT INTO [dbo].[Categories] VALUES (N'MADE IN CHINA', N'20', N'123', N'123213');
GO
INSERT INTO [dbo].[Categories] VALUES (N'MADE IN USA', N'20', N'123', N'aaaa');
GO
INSERT INTO [dbo].[Categories] VALUES (N'DMBEO', N'23', N'123', N'123');
GO
INSERT INTO [dbo].[Categories] VALUES (N'COMEBEO', N'23', N'123', N'123');
GO
INSERT INTO [dbo].[Categories] VALUES (N'tsBEO', N'24', N'123', N'123');
GO
INSERT INTO [dbo].[Categories] VALUES (N'tsbep', N'25', N'123', N'123');
GO
INSERT INTO [dbo].[Categories] VALUES (N'lon', N'26', N'565', N'123');
GO
INSERT INTO [dbo].[Categories] VALUES (N'buoi', N'28', N'234', N'23456');
GO
INSERT INTO [dbo].[Categories] VALUES (N'cc', N'26', N'333', N'333');
GO
INSERT INTO [dbo].[Categories] VALUES (N'vv', N'28', null, null);
GO
-- ----------------------------
-- Records of Customers
-- ----------------------------
INSERT INTO [dbo].[Customers] VALUES (N'khoai', N'123', N'Đang coide');
GO
INSERT INTO [dbo].[Customers] VALUES (N'khoai', N'202cb962ac5975b964b7152d234b70', N'Khoai Vai Chay');
GO




